﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Windows.Forms;
using System.Data.OleDb;
using System.Data;

namespace VietKinsNutrition.Data
{
    public class ClsTblDm_NamhocDAL
    {
        ClsBindingBLL ClsBin = new ClsBindingBLL();
        //Manager.DAL.Access.ClsBindingBLL ClsBin = new Manager.DAL.Access.ClsBindingBLL();
        ////ClsConnection ClsCnn = new ClsConnection();
        public DataTable ReturnTable_Tennamhoc()
        {
            OleDbConnection cn = ClsConnection.OleDBConnect();
            OleDbCommand commad = new OleDbCommand(QueryString.PRCTBLDM_NAMHOC_SELECT, cn);
            DataTable table = new DataTable();
            ////commad.CommandType = CommandType.StoredProcedure;

            try
            {
                commad.ExecuteNonQuery();
                OleDbDataAdapter Adapter = new OleDbDataAdapter(commad);
                Adapter.Fill(table);
                cn.Close();
            }
            catch (System.Exception ex)
            { MessageBox.Show("Error " + ex.Message); }
            return table;
        }
        public DataTable TblNamhoctiep()
        {

            DataSet ReturnSet = new DataSet();

            string NamePrc = "  SELECT TblDm_Namhoc.Id, TblDm_Namhoc.Nambatdau, TblDm_Namhoc.Namketthuc, TblDm_Namhoc.Tennamhoc " +
          " FROM TblDm_Namhoc WHERE (((TblDm_Namhoc.Nambatdau)=(select Namketthuc from TblDm_namHoc where trangthai =-1)))";
            string NameTable = "TblNamhoctiep";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            OleDbCommand Cmm = new OleDbCommand();
            OleDbDataAdapter Dap;
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            //Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open(); Cmm.ExecuteNonQuery();
            }
            catch (Exception Ex)
            {
            }
            finally
            {
                Cmm.Connection.Close();
            }

            Dap = new OleDbDataAdapter(Cmm);
            Dap.Fill(Dset.Tables[0]);
            return Dset.Tables[0];
        }
        public DataSet FindSameName(string Tennamhoc)
        {
            string NamePrc = QueryString.TBLDM_NAMHOC_FINDNAMESAMEPRC.Replace("@Tennamhoc", Tennamhoc);
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Tennamhoc", Tennamhoc));
            DataSet Dset = new DataSet();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Dset);
            return Dset;
        }
        public DataSet Bindin_ComboNamhocIdByNamhocCurrentStand()
        {
            string NamePrc = QueryString.SP_NAMHOC_GETNAMHOCID;
            string NameTable = "TblDm_Namhoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            ClsConnection.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);


            return Dset;
        }
        public DataTable BindingComboNamhocIdByNamhocCurrent()
        {
            string NamePrc = QueryString.SP_NAMHOC_GETNAMHOCID;
            string NameTable = "TblDm_Namhoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            ClsConnection.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);
            int[] iNambatdau = new int[Dset.Tables[0].Rows.Count];
            int[] iNamketthuc = new int[Dset.Tables[0].Rows.Count];
            string[] sNamhoc = new string[Dset.Tables[0].Rows.Count];
            DataTable _table = new DataTable();

            _table.TableName = "TblDm_Namhoc";
            DataColumn AddColum = new DataColumn();

            AddColum.ColumnName = "Namhoc";
            AddColum.DataType = System.Type.GetType("System.String");

            DataColumn IdColum = new DataColumn();

            IdColum.ColumnName = "Id";
            IdColum.DataType = System.Type.GetType("System.Int32");
            int i = 0;
            _table.Columns.Add(IdColum);
            _table.Columns.Add(AddColum);
            foreach (DataRow Row in Dset.Tables["TblDm_Namhoc"].Rows)
            {
                iNambatdau[i] = int.Parse(Row["Nambatdau"].ToString());
                iNamketthuc[i] = int.Parse(Row["Namketthuc"].ToString());
                sNamhoc[i] = iNambatdau[i] + "-" + iNamketthuc[i];
                _table.Rows.Add(Row["Id"].ToString(), sNamhoc[i]);
                i = i + 1;

            }
            return _table;
        }

        public DataTable TblNamhocByCurrent()
        {
            string NamePrc = QueryString.SP_NAMHOC_GETNAMHOCID;
            string NameTable = "TblDm_Namhoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);
            Dset.Tables.Add(NewTable);

            ClsConnection.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);
            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Dset.Tables[0];
        }

        public BindingSource SelectAll()
        {
            string NamePrc = QueryString.SP_NAMHOC_GET;
            string NameTable = "TblDm_Namhoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();

            using (OleDbCommand cmd = new OleDbCommand(QueryString.SP_NAMHOC_GET, ClsConnection.OleDBConnect()))
            {
                cmd.CommandType = CommandType.Text;
                OleDbDataAdapter Adapter = new OleDbDataAdapter(cmd);

                NewTable.TableName = NameTable;

                DataColumn Columm = new DataColumn();
                Columm.ColumnName = Columm.Caption = "STT";
                Columm.DataType = System.Type.GetType("System.Int32");
                Columm.AllowDBNull = false;
                Columm.AutoIncrement = true;
                Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
                NewTable.Columns.Add(Columm);

                Dset.Tables.Add(NewTable);

                Adapter.Fill(Dset.Tables[NameTable]);
            }

            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Binding;
        }

        public DataTable TableNamhocSelectAll()
        {
            string NamePrc = QueryString.SP_NAMHOC_GET;
            string NameTable = "TblDm_Namhoc";
            DataSet Dset = new DataSet();
            DataTable NewTable = new DataTable();
            NewTable.TableName = NameTable;

            DataColumn Columm = new DataColumn();
            Columm.ColumnName = Columm.Caption = "STT";
            Columm.DataType = System.Type.GetType("System.Int32");
            Columm.AllowDBNull = false;
            Columm.AutoIncrement = true;
            Columm.AutoIncrementSeed = Columm.AutoIncrementStep = 1;
            NewTable.Columns.Add(Columm);

            Dset.Tables.Add(NewTable);

            ClsBindingBLL.OleDBSelectAll(NamePrc).Fill(Dset.Tables[NameTable]);

            BindingSource Binding = new BindingSource(Dset, NameTable);
            return Dset.Tables[0];
        }

        int TrangthaiSelected()
        {
            string NamePrc = QueryString.SP_NAMHOC_GETCOUNTSELECTED;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            int i = 0;

            return i;

        }
        public DataTable TblDm_Namhoc_ById(int NamhocId)
        {
            string NamePrc = QueryString.SP_NAMHOC_BYID.Replace("@Id", NamhocId.ToString());
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Parameters.Add(new OleDbParameter("@Id", NamhocId));
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            DataSet Set = new DataSet();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm).Fill(Set);
            return Set.Tables[0];


        }
        public int ReturnNamhocId()
        {
            string NamePrc = QueryString.SP_NAMHOC_GETNAMHOCID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            int i = 0;
            ////Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open();
                i = int.Parse(Cmm.ExecuteScalar().ToString());

            }
            catch (Exception Ex)
            {
                // MessageBox.Show("Bạn chưa đánh dấu năm hoạt động của hệ thống, vào Danh mục năm để thay đổi", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                Cmm.Connection.Close();
            }

            return i;
        }
        public string ReturnTenNamhoc()
        {
            string NamePrc = QueryString.SP_NAMHOC_GETNAMHOCID;
            OleDbCommand Cmm = new OleDbCommand();
            Cmm.Connection = ClsConnection.OleDBConnect();
            Cmm.CommandText = NamePrc;
            string nameNamhoc = "";
            ////Cmm.CommandType = CommandType.StoredProcedure;
            try
            {
                if (Cmm.Connection.State != ConnectionState.Open) Cmm.Connection.Open();
                OleDbDataReader dr = Cmm.ExecuteReader();
                if (dr.Read())
                    nameNamhoc = dr["tennamhoc"].ToString();
            }
            catch (Exception Ex)
            {
                MessageBox.Show("Bạn chưa đánh dấu năm hoạt động của hệ thống, vào Danh mục năm để thay đổi", "Chú ý", MessageBoxButtons.OK, MessageBoxIcon.Warning);
            }
            finally
            {
                Cmm.Connection.Close();
            }

            return nameNamhoc;
        }
        public void Insert(int Nambatdau, int Namketthuc, string Tennamhoc, bool Trangthai)
        {
            string NamePrc = QueryString.SP_NAMHOC_ADD.Replace("@Nambatdau", Nambatdau.ToString()).Replace("@Namketthuc", Namketthuc.ToString()).Replace("@Tennamhoc", Tennamhoc).Replace("@Trangthai", Trangthai.ToString());
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }

        public void Edit(int Id, int Nambatdau, int Namketthuc, string Tennamhoc, bool Trangthai)
        {
            string NamePrc = QueryString.SP_NAMHOC_UPDATE.Replace("@Nambatdau", Nambatdau.ToString()).Replace("@Namketthuc", Namketthuc.ToString()).Replace("@Tennamhoc", Tennamhoc).Replace("@Trangthai", Trangthai.ToString()).Replace("@Id", Id.ToString());
            
            OleDbCommand Cmm = new OleDbCommand();
            ClsConnection.OleDbDAdpter(NamePrc, Cmm);
        }
    }
}
